const express = require('express');
const router = express.Router();
const { authenticateToken } = require('../middleware/auth');
const db = require('../config/database');

router.use(authenticateToken);

// 获取统计数据概览（Web端使用）
router.get('/overview', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { book_id, start_date, end_date } = req.query;
        
        let sql = `
            SELECT 
                SUM(CASE WHEN r.type = 'income' THEN r.amount ELSE 0 END) as total_income,
                SUM(CASE WHEN r.type = 'expense' THEN r.amount ELSE 0 END) as total_expense,
                SUM(CASE WHEN r.type = 'income' THEN r.amount ELSE -r.amount END) as balance
            FROM records r
            JOIN account_books b ON r.book_id = b.book_id
            WHERE r.is_deleted = 0 AND (
                b.owner_user_id = ? OR 
                EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
            )
        `;
        const params = [userId, userId];
        
        if (book_id) {
            sql += ' AND r.book_id = ?';
            params.push(book_id);
        }
        
        if (start_date) {
            sql += ' AND r.date >= ?';
            params.push(start_date);
        }
        
        if (end_date) {
            sql += ' AND r.date <= ?';
            params.push(end_date);
        }
        
        const [overview] = await db.query(sql, params);
        
        // 支出分类占比
        let categorySql = `
            SELECT 
                r.category,
                SUM(r.amount) as amount,
                COUNT(*) as count
            FROM records r
            JOIN account_books b ON r.book_id = b.book_id
            WHERE r.is_deleted = 0 AND r.type = 'expense' AND (
                b.owner_user_id = ? OR 
                EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
            )
        `;
        const categoryParams = [userId, userId];
        
        if (book_id) {
            categorySql += ' AND r.book_id = ?';
            categoryParams.push(book_id);
        }
        
        if (start_date) {
            categorySql += ' AND r.date >= ?';
            categoryParams.push(start_date);
        }
        
        if (end_date) {
            categorySql += ' AND r.date <= ?';
            categoryParams.push(end_date);
        }
        
        categorySql += ' GROUP BY r.category ORDER BY amount DESC';
        
        const [expenseByCategory] = await db.query(categorySql, categoryParams);
        
        // 计算百分比
        const totalExpense = overview[0].total_expense || 0;
        const categoryStats = expenseByCategory.map(item => ({
            ...item,
            percentage: totalExpense > 0 ? ((item.amount / totalExpense) * 100).toFixed(1) : 0
        }));
        
        res.json({
            code: 200,
            data: {
                total_income: parseFloat(overview[0].total_income || 0),
                total_expense: parseFloat(overview[0].total_expense || 0),
                balance: parseFloat(overview[0].balance || 0),
                by_category: categoryStats
            }
        });
    } catch (error) {
        console.error('获取统计数据错误:', error);
        res.status(500).json({ code: 500, message: '获取失败', error: error.message });
    }
});

// 📊 小程序专用：获取指定月份的统计数据
router.get('/monthly', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { book_id, year, month } = req.query;
        
        if (!book_id || !year || !month) {
            return res.status(400).json({ 
                code: 400, 
                message: '缺少必要参数: book_id, year, month' 
            });
        }
        
        // 计算该月的起止日期
        const startDate = `${year}-${String(month).padStart(2, '0')}-01`;
        const endDate = new Date(year, month, 0).getDate(); // 该月最后一天
        const endDateStr = `${year}-${String(month).padStart(2, '0')}-${endDate}`;
        
        let sql = `
            SELECT 
                SUM(CASE WHEN r.type = 'income' THEN r.amount ELSE 0 END) as totalIncome,
                SUM(CASE WHEN r.type = 'expense' THEN r.amount ELSE 0 END) as totalExpense
            FROM records r
            JOIN account_books b ON r.book_id = b.book_id
            WHERE r.is_deleted = 0 
                AND r.book_id = ?
                AND r.date >= ?
                AND r.date <= ?
                AND (
                    b.owner_user_id = ? OR 
                    EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
                )
        `;
        
        const [result] = await db.query(sql, [book_id, startDate, endDateStr, userId, userId]);
        
        res.json({
            code: 200,
            data: {
                totalIncome: parseFloat(result[0]?.totalIncome || 0),
                totalExpense: parseFloat(result[0]?.totalExpense || 0)
            }
        });
    } catch (error) {
        console.error('获取月度统计错误:', error);
        res.status(500).json({ code: 500, message: '获取失败', error: error.message });
    }
});

// 📊 小程序专用：获取分类统计
router.get('/category', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { book_id, year, month, type } = req.query;
        
        if (!book_id || !year || !month || !type) {
            return res.status(400).json({ 
                code: 400, 
                message: '缺少必要参数: book_id, year, month, type' 
            });
        }
        
        // 计算该月的起止日期
        const startDate = `${year}-${String(month).padStart(2, '0')}-01`;
        const endDate = new Date(year, month, 0).getDate();
        const endDateStr = `${year}-${String(month).padStart(2, '0')}-${endDate}`;
        
        let sql = `
            SELECT 
                r.category,
                SUM(r.amount) as total
            FROM records r
            JOIN account_books b ON r.book_id = b.book_id
            WHERE r.is_deleted = 0 
                AND r.book_id = ?
                AND r.type = ?
                AND r.date >= ?
                AND r.date <= ?
                AND (
                    b.owner_user_id = ? OR 
                    EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
                )
            GROUP BY r.category 
            ORDER BY total DESC
        `;
        
        const [categoryData] = await db.query(sql, [book_id, type, startDate, endDateStr, userId, userId]);
        
        res.json({
            code: 200,
            data: categoryData.map(item => ({
                category: item.category,
                total: parseFloat(item.total || 0)
            }))
        });
    } catch (error) {
        console.error('获取分类统计错误:', error);
        res.status(500).json({ code: 500, message: '获取失败', error: error.message });
    }
});

// 📊 网页版：获取多月统计数据（用于柱状图/折线图）
router.get('/chart/monthly', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { book_id, months = 6 } = req.query;
        
        if (!book_id) {
            return res.status(400).json({ 
                code: 400, 
                message: '缺少必要参数: book_id' 
            });
        }
        
        const monthData = [];
        const now = new Date();
        
        // 计算最近N个月的数据
        for (let i = parseInt(months) - 1; i >= 0; i--) {
            const date = new Date(now.getFullYear(), now.getMonth() - i, 1);
            const year = date.getFullYear();
            const month = date.getMonth() + 1;
            const monthStr = `${year}-${String(month).padStart(2, '0')}`;
            
            const startDate = `${year}-${String(month).padStart(2, '0')}-01`;
            const endDate = new Date(year, month, 0).getDate();
            const endDateStr = `${year}-${String(month).padStart(2, '0')}-${endDate}`;
            
            const sql = `
                SELECT 
                    SUM(CASE WHEN r.type = 'income' THEN r.amount ELSE 0 END) as income,
                    SUM(CASE WHEN r.type = 'expense' THEN r.amount ELSE 0 END) as expense
                FROM records r
                JOIN account_books b ON r.book_id = b.book_id
                WHERE r.is_deleted = 0 
                    AND r.book_id = ?
                    AND r.date >= ?
                    AND r.date <= ?
                    AND (
                        b.owner_user_id = ? OR 
                        EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
                    )
            `;
            
            const [result] = await db.query(sql, [book_id, startDate, endDateStr, userId, userId]);
            
            monthData.push({
                month: monthStr,
                income: parseFloat(result[0]?.income || 0),
                expense: parseFloat(result[0]?.expense || 0)
            });
        }
        
        res.json({
            code: 200,
            data: monthData
        });
    } catch (error) {
        console.error('获取月度图表数据错误:', error);
        res.status(500).json({ code: 500, message: '获取失败', error: error.message });
    }
});

// 📊 网页版：获取分类图表数据（用于饼图）
router.get('/chart/category', async (req, res) => {
    try {
        const userId = req.user.user_id;
        const { book_id, type = 'expense' } = req.query;
        
        if (!book_id) {
            return res.status(400).json({ 
                code: 400, 
                message: '缺少必要参数: book_id' 
            });
        }
        
        const sql = `
            SELECT 
                r.category,
                SUM(r.amount) as amount
            FROM records r
            JOIN account_books b ON r.book_id = b.book_id
            WHERE r.is_deleted = 0 
                AND r.book_id = ?
                AND r.type = ?
                AND (
                    b.owner_user_id = ? OR 
                    EXISTS (SELECT 1 FROM book_members WHERE book_id = r.book_id AND user_id = ?)
                )
            GROUP BY r.category 
            ORDER BY amount DESC
        `;
        
        const [categoryData] = await db.query(sql, [book_id, type, userId, userId]);
        
        res.json({
            code: 200,
            data: categoryData.map(item => ({
                category: item.category,
                amount: parseFloat(item.amount || 0)
            }))
        });
    } catch (error) {
        console.error('获取分类图表数据错误:', error);
        res.status(500).json({ code: 500, message: '获取失败', error: error.message });
    }
});

module.exports = router;
